package com.plian.Tools;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

//excel导出，对模板进行插入行或者替换数据工具类

public class DynamicOperateExcelUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(DynamicOperateExcelUtils.class);

    private Workbook workBook;

    private Sheet sheet;

    private static Pattern PATTERN = Pattern.compile("-?[0-9]+(\\.[0-9]+)?");

    public DynamicOperateExcelUtils(InputStream InputStream) {
        this(InputStream, null);
    }

    public DynamicOperateExcelUtils(InputStream inputStream, String[] sheetName) {
        // 解决版本问题，HSSFWorkbook是97-03版本的xls版本，XSSFWorkbook是07版本的xlsx
        try {
            workBook = new XSSFWorkbook(inputStream);
        } catch (Exception e) {
            try {
                workBook = new HSSFWorkbook(inputStream);
            } catch (Exception e1) {
                LOGGER.error("Excel格式不正确", e1);
                throw new RuntimeException(e1);
            }
        }

        for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
            // 进行模板的克隆(接下来的操作都是针对克隆后的sheet)
            sheet = workBook.cloneSheet(0);
            // 移除workbook中的模板sheet
            String sheetname = workBook.getSheetAt(i).getSheetName();
            workBook.removeSheetAt(0);
            // 重命名克隆后的sheet
//            workBook.setSheetName(workBook.getNumberOfSheets()-1, sheetName != null ? sheetname : "sheet"+i);
        }
    }

    public String getCellValue(int rowNum, int colNum) {
        return getCellValue(rowNum, colNum, "");
    }

    /**
     * 根据行号列号获取值
     *
     * @param rowNum       行号
     * @param colNum       列号
     * @param defaultValue 默认值
     * @return
     */
    public String getCellValue(int rowNum, int colNum, String defaultValue) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            return defaultValue;
        }

        Cell cell = row.getCell(colNum);
        if (cell == null) {
            return defaultValue;
        }

        return getCellValue(cell, defaultValue);
    }

    public String getCellValue(Cell cell) {
        return getCellValue(cell, "");
    }

    /**
     * 读取cell的值
     *
     * @param cell         需要读取的cell
     * @param defaultValue 默认值
     * @return
     */
    public String getCellValue(Cell cell, String defaultValue) {
        if (cell != null) {
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();
        }

        return defaultValue;
    }

    /**
     * 替换单元格的内容，单元格的获取位置是合并单元格之前的位置，也就是下标都是合并之前的下表
     *
     * @param cell  单元格
     * @param value 需要设置的值
     */
    public void replaceCellValue(Cell cell, Object value) {
        String val = value != null ? String.valueOf(value) : "";
//        sheet.autoSizeColumn(cell.getColumnIndex(), true);
        if (!Tools.isEmpty(val) && "=".equals(val.substring(0, 1))) {
            try {
                cell.setCellFormula(val.substring(1, val.length()));
                cell.setCellType(CellType.FORMULA);
            } catch (FormulaParseException formulaParseException) {
                cell.setCellValue(val.substring(1, val.length()));
                cell.setCellType(CellType.STRING);
            }
        } else {
            if (checkStrIsnum(val)) {
                cell.setCellValue(new Double(val));
                cell.setCellType(CellType.NUMERIC);
            } else {
                cell.setCellValue(val);
            }
        }
    }
    /**
     * 检验单元格的值是否为数字
     *
     * @return
     */
    private boolean checkStrIsnum(String s) {
        Matcher isNum = PATTERN.matcher(s);
        if (!isNum.matches()) {
            return false;
        }
        return true;
    }

    /**
     * 根据行号，列号进行替换
     *
     * @param rowNum 行号
     * @param colNum 列号
     * @param value  值
     */
    public void replaceCellValue(int rowNum, int colNum, Object value) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            return;
        }

        Cell cell = row.getCell(colNum);
        if (cell == null) {
            return;
        }

        replaceCellValue(cell, value);
    }

    /**
     * 向sheet中添加行，后面的行会向后自动移动
     *
     * @param startRowIndex 起始行
     * @param datas         数据
     * @param keys          数据中Map对应的key
     */
    public void appendRows(int startRowIndex, List<String> datas, String[] keys) {
        // 插入行
        sheet.shiftRows(startRowIndex, sheet.getLastRowNum(), 1, true, false);// 第1个参数是指要开始插入的行，第2个参数是结尾行数,第三个参数表示动态添加的行数
        // 向插入的行中动态的填充数据
        // 创建行
        Row row = sheet.createRow(startRowIndex);
        Row rowlast = sheet.getRow(startRowIndex - 1);
        // 添加单元格
        Cell cell = null;
        for (int j = 0, length_2 = keys.length; j < length_2; j++) {
//                String key = keys[j];
            String value =  datas.get(j) != null ? String.valueOf(datas.get(j)) : "";
            cell = row.createCell(j);

            String celltype = keys[j].split(",")[1];
            cell.setCellStyle(rowlast.getCell(j).getCellStyle());
            if (!Tools.isEmpty(value) && "=".equals(value.substring(0, 1))) {
                cell.setCellFormula(value.substring(1, value.length()));
                cell.setCellType(CellType.FORMULA);
            } else {
                if ("1".equals(celltype)&&checkStrIsnum(value)) {
                    cell.setCellValue(new Double(value));
                    cell.setCellType(CellType.NUMERIC);
                } else {
                    cell.setCellValue(value);
                }
            }
//            cell.setCellValue(value);
        }

        // 调整列宽
//        autoResizeColumn(keys.length);
    }

    public void exportExcel(File file) {
        exportExcel(file.getAbsolutePath());
    }

    public void exportExcel(String fileFullPath) {
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(fileFullPath);
            workBook.write(outputStream);
        } catch (IOException e) {
            LOGGER.error(" exportExcel error", e);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }

    private void autoResizeColumn(int colNumber) {
        // 如果是SXSSFSheet，需要调用trackAllColumnsForAutoSizing方法一次
        if (sheet instanceof SXSSFSheet) {
            SXSSFSheet tmpSheet = (SXSSFSheet) sheet;
            tmpSheet.trackAllColumnsForAutoSizing();
        }

        for (int i = 0; i < colNumber; i++) {
            sheet.autoSizeColumn(i, true);
        }
    }

    public Sheet getSheet(int i) {
        sheet = workBook.getSheetAt(i);
        return sheet;
    }

    public Workbook getWorkbook() {
        return workBook;
    }
}
